'''
Created on Aug 18, 2012

@author: petrina
'''
import logging
import psycopg2
from datastructure.link import Link
from datastructure.linkset import Linkset
from datastructure.agg_record import Agg_record
from error.data_exceptions import Link_exception, Record_exception



class Database_input(object):
    '''
    This class manages the import of data to the datastructures.
    
    Call import_data() and get_links() in order to retrieve the filled linkset.
    '''


    def __init__(self,conf):
        self.conf = conf
        self._links = {}
        
        # further application might demand an user specific value from the config
        # number of time intervals of one day
        self._intervals = 96
        self.__int_size = 15
        
        self.__is_kph = self.conf.getboolean("analysis", "is_kph")
        self.__conn = psycopg2.connect(host=conf.get("db","host"), 
                                     database=conf.get("db","name"), 
                                     user=conf.get("db","user"), 
                                     password=conf.get("db","pass"))



    #----------------------public methods of the class-------------------------


    def import_data(self):
        self.import_links()                                                                                                                                                                                
        self.import_records()                                                                                                                                                                             
        self.import_timeseries()                                                                                                                                                                          
        self.import_reference()      
        
                                                                                                                                                          
    def import_links(self):
        self.import_link_table()
        self.import_free_speeds()
        
        if len(self._links) > 0: 
            logging.info("Database_input: Imported %d links" % len(self._links))
        else: 
            logging.error("Database_input: no Links found - cannot import links")
            raise Link_exception("query did not find any links")
        

    def import_link_table(self):
        cur = self.__conn.cursor()
        
        imp_all_links = (self.conf.get("analysis", "links").lower().strip() == "*")


        query = "SELECT road_id, lane_id, length FROM " + self.conf.get("db","table_link") 
        logging.info("Getting links from DB: " + query)
        cur.execute(query)

        relevant_links = [s.strip() for s in self.conf.get("analysis", "links").split(",")]
        for (rid,lid,length) in cur:
            id = self.get_road_id(rid,lid) 
            if imp_all_links or id in relevant_links:
                direction = True if lid==0 else False
                self._links[id] = Link(id=rid,direction=direction,length=length,is_kph= self.__is_kph,intervals=self._intervals)
                

    def import_free_speeds(self):
        cur = self.__conn.cursor()

        query = "SELECT road_id,lane_id, free_speed FROM " + self.conf.get("db", "table_ffspeed")
        logging.info("Getting freespeeds from DB: " + query)
        cur.execute(query)

        for (rid,lid,speed) in cur: 
            id = self.get_road_id(rid,lid)
            if id in self._links:
                self._links[id].freespeed=speed
                logging.debug("Set speed for " + id)


    def get_road_id(self,rid,lid):
        return "%d:%d" % (rid,lid) 
    

    def import_records(self):
        cur = self.__conn.cursor()

        # build query (configuration might include time-limites, if not use no time-limits)
        query = "SELECT road_id, lane_id, start_time, speed, number_datapoints, coverage FROM " + self.conf.get("db","table_data")     
        if self.conf.has_option("analysis", "begintime") or self.conf.has_option("analysis", "endtime"):
            query += " WHERE "
            if self.conf.has_option("analysis", "begintime"):
                query += "start_time > '" + self.conf.get("analysis", "begintime") + "'"
            if self.conf.has_option("analysis", "begintime") and self.conf.has_option("analysis", "endtime"):
                query += " AND "     
            if self.conf.has_option("analysis", "endtime"):
                query += "start_time <= '" + self.conf.get("analysis", "endtime") + "'"
                
        
                        
        logging.info("Getting records: " + query)
        cur.execute(query)

        imported_count=0
        
        
        for (rid,lid, time, speed,count, coverage) in cur:
            id = self.get_road_id(rid,lid)
            if id not in self._links: 
                continue
            link = self._links[id]
            rec = Agg_record(speed=speed, count=count, datasource="db", 
                             timestamp=time, link=link, coverage=coverage, 
                             is_kph = self.__is_kph, int_size = self.__int_size)
            link.records.add_record(rec)
            imported_count+=1
        
        if imported_count > 0: 
            logging.info("Imported %d records" % imported_count)
        else:
            logging.error("Database_input: could not find or import any records - you might run into problems with the indicator calculation")
            
            # without a single record there is no sense in any further imports or calculations
            raise Record_exception("could not find or import the aggregated records (from FLEET)")

    def import_timeseries(self):
        cur = self.__conn.cursor()

        query = "SELECT road_id, lane_id, start_time, speed_smoothed, std, number_datapoints FROM " + self.conf.get("db", "table_timeseries") \
                + " WHERE  date(start_time) = '" + self.conf.get("analysis", "timeseries_date") + "'"

        logging.info("Loading timeseries. " + query)
        cur.execute(query)

        imported_count=0
        for(rid,lid,time,speed,std,count) in cur:
            id = self.get_road_id(rid,lid)
            if id not in self._links:
                continue

            link = self._links[id]
            rec = Agg_record(speed = speed, count = count,datasource="db", timestamp =time, 
                             link =link, std=std, is_kph = self.__is_kph, int_size = self.__int_size) 
            link.timeseries.add_record(rec)
            imported_count+=1

        logging.info("Imported %d timeseries values" % imported_count)
        
        if imported_count > 0: 
            logging.info("Imported %d timeseries values" % imported_count)
        else:
            logging.error("Database_input: could not find or import any timeseries - you might run into problems with the indicator calculation")
            


    def import_reference(self):
        cur = self.__conn.cursor()                                                                                                                                                                     
                                                                                                                                                                                                     
        query = "SELECT road_id, lane_id, start_time, speed, number_datapoints, coverage FROM " + self.conf.get("db","table_reference_data")                                                                   
        
        if self.conf.has_option("analysis", "begintime") or self.conf.has_option("analysis", "endtime"):
            query += " WHERE "
            if self.conf.has_option("analysis", "begintime"):
                query += "start_time > '" + self.conf.get("analysis", "begintime") + "'"
            if self.conf.has_option("analysis", "begintime") and self.conf.has_option("analysis", "endtime"):
                query += " AND "     
            if self.conf.has_option("analysis", "endtime"):
                query += "start_time <= '" + self.conf.get("analysis", "endtime") + "'"
        
        
        logging.info("Getting reference records: " + query)                                                                                                                                                    
        cur.execute(query)                                                                                                                                                                           
                                                                                                                                                                                                     
        imported_count=0                                                                                                                                                                             
        for (rid,lid, time, speed,count, coverage) in cur:                                                                                                                                           
            id = self.get_road_id(rid,lid)                                                                                                                                                           
            if id not in self._links:                                                                                                                                                                 
                continue
                                                                                                                                                             
            link = self._links[id]                                                                                                                                                                    
            rec = Agg_record(speed=speed, count =count ,datasource="db" ,timestamp=time, link=link, 
                             coverage=coverage, is_kph = self.__is_kph, int_size = self.__int_size)
            link.reference.add_record(rec)                                                                                                                                                         
            imported_count+=1                                                                                                                                                                        
        
                                                                                                                                                                             
                                                                                                                                             
        if imported_count > 0: 
            logging.info("Imported %d reference records" % imported_count)            
        else:
            logging.error("Database_input: could not find or import any reference records - you might run into problems with the indicator calculation")
            


    def get_links(self):
        lset = Linkset()
        for link in self._links.values(): 
            try:
                lset.add_link(link)
            except:
                # did not found data for the link because its still 
                pass
        
        return lset

  
    
    def aggregate(self, min):
        '''
        aggregates the records, reference-data and timeseries
        
        @param min: number of minutes per time interval 
        '''
        
        logging.info("Database_input: starting to aggregate records")
        
        for link in self._links.values():
            link.records = link.records.aggregated_List(min, link) 
            link.reference = link.reference.aggregated_List(min, link) 
            link.timeseries = link.timeseries.aggregated_List(min, link) 